package com.F06.business;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import jxl.write.Label;
import jxl.write.Number;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.F06.action.F060101Action;
import com.F06.actionForm.F060101ActionForm;
import com.F06.actionForm.F060102ActionForm;
import com.F06.dao.F060101DAO;
import com.F06.dao.F060102DAO;
import com.action.BaseAction;
import com.util.ExcelExport;

public class F060102Business {

	public static void writeExcel(String modlePath, String outName,F060102DAO dao,F060102ActionForm actionForm) {
		/** 记录操作日志 */
		Logger log = Logger.getLogger(F060102Business.class);
		String time = actionForm.getSignaure_date_year()+"-1-1";
		log.info("time = "+time);
		WritableWorkbook wwb = null;
		Workbook workbook = null;
		List sheet0 = new ArrayList();
		List sheet0sum = new ArrayList();
		Map sheetmap = new HashMap(); 
		Map sheetmapsum = new HashMap(); 
		String all = "all";// 单元格类型的参数
		String none = "none";// 单元格类型的参数
		String center = "center";// 单元格类型的参数
		try {
			try {
				// 根据模板去创建新的工作簿
				InputStream ins = new FileInputStream(modlePath);
				workbook = Workbook.getWorkbook(ins);
				File outFile = new File(outName);
				wwb = Workbook.createWorkbook(outFile, workbook);
			} catch (BiffException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if (wwb != null) {
				try {
					// 得到工作表第一页
					WritableSheet wsheet0 = wwb.getSheet(0);
					if (wsheet0 != null) {
						log.info(" wsheet0 " + wsheet0.getColumns());
						sheet0=dao.sheet0(time);
						if(sheet0!=null&&sheet0.size()>0){
							int sheet0hh = 3;
							int v = 1;
							int m = 0;
							int y = 3 ;
							for (int i = 0; i<sheet0.size(); i++) {
								sheetmap = (Map) sheet0.get(i);
								wsheet0.addCell(new Label(0, sheet0hh, String.valueOf(v),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Label(1, sheet0hh, sheetmap.get("型号").toString(),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Number(2,sheet0hh,Double.parseDouble(sheetmap.get("材料成本").toString()),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Number(3,sheet0hh,Double.parseDouble(sheetmap.get("人工").toString()),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Number(4,sheet0hh,Double.parseDouble(sheetmap.get("运费").toString()),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Number(5,sheet0hh,Double.parseDouble(sheetmap.get("佣金").toString()),ExcelExport.getStyle(8,"0")));
								wsheet0.addCell(new Number(6,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量1").toString()),ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Number(7,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量1").toString()),ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Formula(8,sheet0hh,"J"+(sheet0hh+1)+"/H"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Number(9,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税1").toString()),ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Formula(10,sheet0hh,"C"+(sheet0hh+1)+"/H"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Formula(11,sheet0hh,"(1-K"+(sheet0hh+1)+"/J"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"1")));
								wsheet0.addCell(new Number(12,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量2").toString()),ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Number(13,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量2").toString()),ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Formula(14,sheet0hh,"P"+(sheet0hh+1)+"/N"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Number(15,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税2").toString()),ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Formula(16,sheet0hh,"C"+(sheet0hh+1)+"/N"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Formula(17,sheet0hh,"(1-Q"+(sheet0hh+1)+"/p"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"2")));
								wsheet0.addCell(new Number(18,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量3").toString()),ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Number(19,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量3").toString()),ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Formula(20,sheet0hh,"V"+(sheet0hh+1)+"/T"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Number(21,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税3").toString()),ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Formula(22,sheet0hh,"C"+(sheet0hh+1)+"/T"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Formula(23,sheet0hh,"(1-W"+(sheet0hh+1)+"/V"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"3")));
								wsheet0.addCell(new Number(24,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量4").toString()),ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Number(25,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量4").toString()),ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Formula(26,sheet0hh,"AB"+(sheet0hh+1)+"/Z"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Number(27,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税4").toString()),ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Formula(28,sheet0hh,"C"+(sheet0hh+1)+"/Z"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Formula(29,sheet0hh,"(1-AC"+(sheet0hh+1)+"/AB"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"4")));
								wsheet0.addCell(new Number(30,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量5").toString()),ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Number(31,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量5").toString()),ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Formula(32,sheet0hh,"AH"+(sheet0hh+1)+"/AF"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Number(33,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税5").toString()),ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Formula(34,sheet0hh,"C"+(sheet0hh+1)+"/AF"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Formula(35,sheet0hh,"(1-AI"+(sheet0hh+1)+"/AH"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"5")));
								wsheet0.addCell(new Number(36,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量6").toString()),ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Number(37,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量6").toString()),ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Formula(38,sheet0hh,"AN"+(sheet0hh+1)+"/AL"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Number(39,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税6").toString()),ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Formula(40,sheet0hh,"C"+(sheet0hh+1)+"/AL"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Formula(41,sheet0hh,"(1-AO"+(sheet0hh+1)+"/AN"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"6")));
								wsheet0.addCell(new Number(42,sheet0hh,Double.parseDouble(sheetmap.get("计划订单数量7").toString()),ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Number(43,sheet0hh,Double.parseDouble(sheetmap.get("实际订单数量7").toString()),ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Formula(44,sheet0hh,"AT"+(sheet0hh+1)+"/AR"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Number(45,sheet0hh,Double.parseDouble(sheetmap.get("定货值不含税7").toString()),ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Formula(46,sheet0hh,"C"+(sheet0hh+1)+"/AR"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Formula(47,sheet0hh,"(1-AU"+(sheet0hh+1)+"/AT"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"7")));
								wsheet0.addCell(new Number(48,sheet0hh,Double.parseDouble("0"),ExcelExport.getStyle(8,"8")));
								wsheet0.addCell(new Formula(49,y,"H"+(sheet0hh+1)+"+N"+(sheet0hh+1)+"+T"+(sheet0hh+1)+"+Z"+(sheet0hh+1)+"+AF"+(sheet0hh+1)+"+AL"+(sheet0hh+1)+"+AR"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"8")));
								wsheet0.addCell(new Formula(50,y,"J"+(sheet0hh+1)+"+P"+(sheet0hh+1)+"+V"+(sheet0hh+1)+"+AB"+(sheet0hh+1)+"+AH"+(sheet0hh+1)+"+AN"+(sheet0hh+1)+"+AT"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"8")));
								wsheet0.addCell(new Formula(51,y,"K"+(sheet0hh+1)+"+Q"+(sheet0hh+1)+"+W"+(sheet0hh+1)+"+AC"+(sheet0hh+1)+"+AI"+(sheet0hh+1)+"+AO"+(sheet0hh+1)+"+AU"+(sheet0hh+1)+"",ExcelExport.getStyle(8,"8")));
								wsheet0.addCell(new Formula(52,sheet0hh,"(1-AZ"+(sheet0hh+1)+"/AY"+(sheet0hh+1)+")*100",ExcelExport.getStyle(8,"8")));
								sheet0hh = sheet0hh + 1;
								m = sheet0hh + 1;
								v++;
								y=v++;
								log.info("y = "+y);
							}
							int n = m-1;
							wsheet0.addCell(new Label(0, n, " ",ExcelExport.getStyle(8,"0")));
							wsheet0.addCell(new Label(1, n, "合计".toString(),ExcelExport.getStyle(8,"0")));
							wsheet0.addCell(new Formula(2, n, "SUM(C4:C"+n+")", ExcelExport.getStyle(8,"0")));
							wsheet0.addCell(new Formula(3, n,"SUM(D4:D"+n+")", ExcelExport.getStyle(8,"0"))); 
							wsheet0.addCell(new Formula(4,n,"SUM(E4:E"+n+")",ExcelExport.getStyle(8,"0")));
							wsheet0.addCell(new Formula(5,n,"SUM(F4:F"+n+")",ExcelExport.getStyle(8,"0")));
							wsheet0.addCell(new Formula(6,n,"SUM(G4:G"+n+")",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(7,n,"SUM(H4:H"+n+")",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(8,n,"SUM(I4:I"+n+")",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(9,n,"SUM(J4:J"+n+")",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(10,n,"SUM(K4:K"+n+")",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(11,n,"1-K"+n+"/J"+n+"",ExcelExport.getStyle(8,"1")));
							wsheet0.addCell(new Formula(12,n,"SUM(M4:M"+n+")",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(13,n,"SUM(N4:N"+n+")",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(14,n,"SUM(O4:O"+n+")",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(15,n,"SUM(P4:P"+n+")",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(16,n,"SUM(Q4:Q"+n+")",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(17,n,"1-Q"+n+"/p"+n+"",ExcelExport.getStyle(8,"2")));
							wsheet0.addCell(new Formula(18,n,"SUM(S4:S"+n+")",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(19,n,"SUM(T4:T"+n+")",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(20,n,"SUM(U4:U"+n+")",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(21,n,"SUM(V4:V"+n+")",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(22,n,"SUM(W4:W"+n+")",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(23,n,"1-W"+n+"/V"+n+"",ExcelExport.getStyle(8,"3")));
							wsheet0.addCell(new Formula(24,n,"SUM(Y4:Y"+n+")",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(25,n,"SUM(Z4:Z"+n+")",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(26,n,"SUM(AA4:AA"+n+")",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(27,n,"SUM(AB4:AB"+n+")",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(28,n,"SUM(AC4:AC"+n+")",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(29,n,"1-AC"+n+"/AB"+n+"",ExcelExport.getStyle(8,"4")));
							wsheet0.addCell(new Formula(30,n,"SUM(AE4:AE"+n+")",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(31,n,"SUM(AF4:AF"+n+")",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(32,n,"SUM(AG4:AG"+n+")",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(33,n,"SUM(AH4:AH"+n+")",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(34,n,"SUM(AI4:AI"+n+")",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(35,n,"1-AI"+n+"/AH"+n+"",ExcelExport.getStyle(8,"5")));
							wsheet0.addCell(new Formula(36,n,"SUM(AK4:AK"+n+")",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(37,n,"SUM(AL4:AL"+n+")",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(38,n,"SUM(AM4:AM"+n+")",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(39,n,"SUM(AN4:AN"+n+")",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(40,n,"SUM(AO4:AO"+n+")",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(41,n,"1-AO"+n+"/AN"+n+"",ExcelExport.getStyle(8,"6")));
							wsheet0.addCell(new Formula(42,n,"SUM(AQ4:AQ"+n+")",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(43,n,"SUM(AR4:AR"+n+")",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(44,n,"SUM(AS4:AS"+n+")",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(45,n,"SUM(AT4:AT"+n+")",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(46,n,"SUM(AU4:AU"+n+")",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(47,n,"1-AU"+n+"/AT"+n+"",ExcelExport.getStyle(8,"7")));
							wsheet0.addCell(new Formula(48,n,"SUM(AW4:AW"+n+")",ExcelExport.getStyle(8,"8")));
							wsheet0.addCell(new Formula(49,n,"H"+(n+1)+"+N"+(n+1)+"+T"+(n+1)+"+Z"+(n+1)+"+AF"+(n+1)+"+AL"+(n+1)+"+AR"+(n+1)+"",ExcelExport.getStyle(8,"8")));
							wsheet0.addCell(new Formula(50,n,"J"+(n+1)+"+P"+(n+1)+"+V"+(n+1)+"+AB"+(n+1)+"+AH"+(n+1)+"+AN"+(n+1)+"+AT"+(n+1)+"",ExcelExport.getStyle(8,"8")));
							wsheet0.addCell(new Formula(51,n,"K"+(n+1)+"+Q"+(n+1)+"+W"+(n+1)+"+AC"+(n+1)+"+AI"+(n+1)+"+AO"+(n+1)+"+AU"+(n+1)+"",ExcelExport.getStyle(8,"8")));
							wsheet0.addCell(new Formula(52,n,"1-AZ"+(n+1)+"/AY"+(n+1)+"",ExcelExport.getStyle(8,"8")));
							
							wsheet0.mergeCells(0, m, 5, m);
							wsheet0.mergeCells(6, m, 11, m);
							wsheet0.mergeCells(12, m, 17, m);
							wsheet0.mergeCells(18, m, 23, m);
							wsheet0.mergeCells(24, m, 29, m);
							wsheet0.mergeCells(30, m, 35, m);
							wsheet0.mergeCells(36, m, 41, m);
							wsheet0.mergeCells(42, m, 47, m);
							wsheet0.mergeCells(48, m, 52, m);
							wsheet0.addCell(new Label(0, m, "市场分类毛利 Gross Margin",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(6,m,"L"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(12, m, "R"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(18, m, "X"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(24, m, "AD"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(30, m, "AJ"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(36, m, "AP"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(42, m, "AV"+y+"",ExcelExport.getStyle(8,"9")));
							wsheet0.addCell(new Formula(48, m, "BA"+y+"",ExcelExport.getStyle(8,"9")));
							log.info(" wsheet0 " + wsheet0.getColumns());
						}else {
							wsheet0.addCell(new Label(0, 8, "数据查询结果为空"));
						}
					//	wwb.write();
					} 
					wwb.write();
					actionForm.setErrorFlag("7");
				} catch (RowsExceededException e) {
					actionForm.setErrorFlag("6");
					e.printStackTrace();
				} catch (WriteException e) {
					actionForm.setErrorFlag("6");
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			System.out.print("写入Excel出错");
			e.printStackTrace();
		} finally {
			try {
				workbook.close();
				wwb.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Times New Roman 设置excel字体及单元格样式
	 * 
	 * @param size
	 *            字体大小
	 * @param style
	 *            单元格样式
	 * @return
	 */
	public static WritableCellFormat getNewRoman(int size, String style) {
		WritableFont wf = new WritableFont(WritableFont
				.createFont("Times New Roman"), size, WritableFont.NO_BOLD,
				false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try {
			wcfF.setAlignment(Alignment.RIGHT);
			/** 把水平对齐方式指定为居右 */
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
			/** 把垂直对齐方式指定为居中 */
			// wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if ("all".equals(style)) {
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			} else if ("none".equals(style)) {
				// 单元格无边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			} else if ("center".equals(style)) {
				wcfF.setAlignment(Alignment.CENTRE);
				/** 把水平对齐方式指定为居右 */
				// 单元格有边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return wcfF;
	}

	/**
	 * 宋体 设置excel字体及单元格样式
	 * 
	 * @param size
	 *            字体大小
	 * @param style
	 *            单元格样式
	 * @return
	 */
	public static WritableCellFormat getSongTi(int size, String style) {
		WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), size,
				WritableFont.NO_BOLD, false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try {
			wcfF.setAlignment(Alignment.LEFT);
			/** 把水平对齐方式指定为居左 */
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
			/** 把垂直对齐方式指定为居中 */
			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if ("all".equals(style)) {
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			} else if ("none".equals(style)) {
				// 单元格无边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			} else if ("title".equals(style)) {
				wcfF.setAlignment(Alignment.CENTRE);
				/** 把水平对齐方式指定为居中 */
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return wcfF;
	}
}
